﻿/*  
 *  IIS LogFile Analyser, Copyright (C) 2013. http://www.iis-analyser.com
 *  --
 *  This program is free software, provided "AS IS" WITHOUT ANY WARRANTY; without even the implied warranty of 
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE
 * 
 *  File Author: Ian Quigley,    2013-02-23
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;

namespace DataStore
{
    public abstract class ReferenceRecord
    {
        private long _id;
        private string _name;

        [FieldDescription("Id", "Long, Primary Key")]
        public long Id 
        {
            get 
            {
                if (_id == 0 && !string.IsNullOrWhiteSpace( _name ))
                    _id = FindOrCreate( _name).Id;

                return _id; 
            }
            set
            {
                if (_id == value)
                    return;

                _id = value;
                _name = FindById( _id).Name;
            }
        }

        [FieldDescription("Name", "String, Reference data text")]
        public string Name 
        { 
            get
            {
                if (_id == 0 && string.IsNullOrWhiteSpace(_name))
                    _name = FindById( _id).Name;

                return _name;
            }
            set
            {
                if (_name == value)
                    return;
                
                _name = value;
                _id = FindOrCreate( _name).Id;
            }
        }

        protected abstract string TableName { get; }
             
        internal abstract ReferenceRecord Create( );

        internal void InitialiseTable()
        {
            using (SQLiteConnection connection = new SQLiteConnection( Database.DataSource ))
            using (SQLiteCommand command = new SQLiteCommand( "CREATE TABLE if not exists " + TableName + " (Id Integer PRIMARY KEY AutoIncrement, Name Text)", connection ))
            {
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }
        }

        internal void Save()
        {
            using (SQLiteConnection connection = new SQLiteConnection( Database.DataSource ))
            using (SQLiteCommand command = new SQLiteCommand( "INSERT OR REPLACE INTO " + TableName + " (Name) VALUES (@p0); select last_insert_rowid();", connection ))
            {
                command.Parameters.AddWithValue( "@p0", Name );
                connection.Open();
                _id = (long)command.ExecuteScalar();
                connection.Close();
                _name = Name;
            }
        }

        internal ReferenceRecord FindOrCreate( string name) 
        {
            using (SQLiteConnection connection = new SQLiteConnection(Database.DataSource))
            using (SQLiteCommand command = new SQLiteCommand( "SELECT Id, Name FROM " + TableName + " WHERE Name = @p0", connection))
            {
                connection.Open();
                command.Parameters.AddWithValue( "@p0", name );                
                var reader = command.ExecuteReader();
                
                ReferenceRecord record;
                if (!reader.Read())
                {
                    record = Create();
                    record._name = name;
                    record.Save();
                    connection.Close();
                    return record;
                }

                record = Create( );
                record._id = (long)reader[0];
                record._name = (string)reader[1];
                return record;
            }
        }

        internal ReferenceRecord FindById( long id)
        {
            using (SQLiteConnection connection = new SQLiteConnection( Database.DataSource ))
            using (SQLiteCommand command = new SQLiteCommand( "SELECT Id, Name FROM " + TableName + " WHERE Id = @p0", connection ))
            {
                command.Parameters.AddWithValue( "@p0", id );
                connection.Open();
                var reader = command.ExecuteReader();

                if (!reader.Read())
                {
                    connection.Close();
                    return null;
                }
                connection.Close();

                var record = Create( );
                record._id = id;
                record._name = (string)reader[1];
                return record;
            }

        }
    }
}
